"""
数据库管理类
"""
import sqlite3
import os
import threading
import time
from typing import Dict, List, Any, Tuple, Optional
from queue import Queue, Empty
from contextlib import contextmanager

class DatabaseConnectionPool:
    def __init__(self, db_path: str, max_connections: int = 5, max_retries: int = 3):
        self.db_path = db_path
        self.max_connections = max_connections
        self.connections = Queue(maxsize=max_connections)
        self._lock = threading.Lock()
        
        # 初始化连接池
        for i in range(max_connections):
            conn = self._create_connection(max_retries)
            if conn:
                self.connections.put(conn)
    
    def _create_connection(self, max_retries: int = 3) -> Optional[sqlite3.Connection]:
        """创建一个新的数据库连接"""
        for attempt in range(max_retries):
            try:
                # 设置更长的超时时间和检查间隔
                conn = sqlite3.connect(self.db_path, timeout=30.0)
                
                # 配置连接
                conn.execute("PRAGMA busy_timeout = 30000")  # 30秒超时
                conn.execute("PRAGMA journal_mode=DELETE")   # 使用默认日志模式
                conn.execute("PRAGMA synchronous=NORMAL")    # 降低同步级别
                conn.isolation_level = None  # 自动提交模式
                
                return conn
            except sqlite3.Error as e:
                if attempt == max_retries - 1:
                    print(f"创建数据库连接失败: {str(e)}")
                    return None
                time.sleep(0.1 * (attempt + 1))
        return None
    
    @contextmanager
    def get_connection(self, max_retries: int = 3, retry_delay: float = 0.1):
        conn = None
        for attempt in range(max_retries):
            try:
                conn = self.connections.get(timeout=5.0)
                break
            except Empty:
                if attempt < max_retries - 1:
                    time.sleep(retry_delay)
                else:
                    # 如果无法从池中获取连接，尝试创建新连接
                    conn = self._create_connection()
                    if not conn:
                        raise Exception("无法获取数据库连接")
                    break
        
        if not conn:
            raise Exception("无法获取数据库连接")
            
        try:
            yield conn
        finally:
            try:
                # 检查连接是否有效
                conn.execute("SELECT 1")
                self.connections.put(conn)
            except sqlite3.Error:
                # 如果连接无效，创建新连接放回池中
                try:
                    conn.close()
                except:
                    pass
                new_conn = self._create_connection()
                if new_conn:
                    self.connections.put(new_conn)
    
    def close_all(self):
        """关闭所有连接"""
        while not self.connections.empty():
            try:
                conn = self.connections.get_nowait()
                try:
                    conn.close()
                except:
                    pass
            except Empty:
                break

class DatabaseManager:
    _instance = None
    _lock = threading.Lock()
    
    @classmethod
    def get_instance(cls, db_path: str = None):
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = DatabaseManager(db_path)
        return cls._instance
    
    @staticmethod
    def get_database_path() -> str:
        """获取数据库文件的路径"""
        return os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), "data", "uudata.db")

    def __init__(self, db_path: str = None):
        """初始化数据库管理器"""
        self.db_path = db_path if db_path else self.get_database_path()
        self._ensure_db_directory()
        self.pool = None
        self._init_pool()
        
    def _init_pool(self, max_retries: int = 3):
        """初始化连接池"""
        for attempt in range(max_retries):
            try:
                self.pool = DatabaseConnectionPool(self.db_path)
                break
            except Exception as e:
                if attempt == max_retries - 1:
                    raise Exception(f"初始化数据库连接池失败: {str(e)}")
                time.sleep(0.1 * (attempt + 1))
        
    def _ensure_db_directory(self):
        """确保数据库文件所在目录存在"""
        db_dir = os.path.dirname(self.db_path)
        if db_dir and not os.path.exists(db_dir):
            os.makedirs(db_dir)

    def execute(self, query: str, parameters: tuple = (), max_retries: int = 3) -> Optional[List[Tuple]]:
        """执行SQL查询"""
        last_error = None
        for attempt in range(max_retries):
            try:
                with self.pool.get_connection() as conn:
                    cursor = conn.cursor()
                    cursor.execute(query, parameters)
                    if query.strip().upper().startswith("SELECT"):
                        result = cursor.fetchall()
                        cursor.close()
                        return result
                    else:
                        conn.commit()
                        cursor.close()
                        return None
            except sqlite3.Error as e:
                last_error = e
                if attempt < max_retries - 1:
                    time.sleep(0.1 * (attempt + 1))
                    continue
                raise Exception(f"SQL执行错误: {str(e)}")
        
        if last_error:
            raise Exception(f"SQL执行错误，重试{max_retries}次后失败: {str(last_error)}")

    def select(self, table_name: str, columns: List[str] = None, where: str = None, 
               parameters: tuple = (), order_by: str = None, limit: int = None, offset: int = None) -> List[Tuple]:
        """查询数据"""
        columns_str = "*" if not columns else ", ".join(columns)
        query = f"SELECT {columns_str} FROM {table_name}"
        if where:
            query += f" WHERE {where}"
        if order_by:
            query += f" ORDER BY {order_by}"
        if limit is not None:
            query += f" LIMIT {limit}"
            if offset is not None:
                query += f" OFFSET {offset}"
        return self.execute(query, parameters)

    def create_table(self, table_name: str, columns: Dict[str, str], if_not_exists: bool = True):
        """创建表"""
        columns_str = ", ".join([f"{col} {definition}" for col, definition in columns.items()])
        exists_clause = "IF NOT EXISTS" if if_not_exists else ""
        query = f"CREATE TABLE {exists_clause} {table_name} ({columns_str})"
        self.execute(query)
        
    def insert(self, table_name: str, data: Dict[str, Any]):
        """插入数据"""
        columns = list(data.keys())
        values = list(data.values())
        placeholders = ", ".join(["?" for _ in values])
        query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders})"
        self.execute(query, tuple(values))
        
    def update(self, table_name: str, data: Dict[str, Any], where: str = None, parameters: tuple = ()):
        """更新数据"""
        set_clause = ", ".join([f"{col} = ?" for col in data.keys()])
        query = f"UPDATE {table_name} SET {set_clause}"
        if where:
            query += f" WHERE {where}"
        self.execute(query, tuple(data.values()) + parameters)
        
    def delete(self, table_name: str, where: str = None, parameters: tuple = ()):
        """删除数据"""
        query = f"DELETE FROM {table_name}"
        if where:
            query += f" WHERE {where}"
        self.execute(query, parameters)

    def table_exists(self, table_name: str) -> bool:
        """检查表是否存在"""
        query = "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
        result = self.execute(query, (table_name,))
        return bool(result)

    def get_table_info(self, table_name: str) -> List[dict]:
        """获取表的结构信息"""
        query = f"PRAGMA table_info({table_name})"
        return self.execute(query)

    def __del__(self):
        """析构函数"""
        if hasattr(self, 'pool') and self.pool:
            self.pool.close_all()
